from django_sqlgen.test.models import AdditionalCustomerData
from django_sqlgen.test.models import Customer
from django_sqlgen.core import SELECT
from django_sqlgen.core import ModelSource
from django_sqlgen.core import COUNT
from django_sqlgen.core import UNION
from django_sqlgen.core import UNION_ALL
from django_sqlgen.test.models import CustomerGroup
import datetime
import unittest

def check(test_case, expr, expected):
    result = list(expr.query())
    expected = list(expected)

    try:
        test_case.assertEquals(result, expected)
    finally:
        print "===================================="
        print expr.get_sql()
        print "===================================="

class MyFuncTestCase(unittest.TestCase):
    def setUp(self):
        self.c1 = Customer.objects.create(type='A', moneySpent = 10)
        self.c2 = Customer.objects.create(type='A', moneySpent = 20)
        self.c3 = Customer.objects.create(type='N', moneySpent = 30)
        self.c4 = Customer.objects.create(type='N', moneySpent = 40)
        self.c5 = Customer.objects.create(type='N', moneySpent = 50)
        AdditionalCustomerData.objects.create(customer = self.c1, name='c1')
        AdditionalCustomerData.objects.create(customer = self.c2, name='c2')
        AdditionalCustomerData.objects.create(customer = self.c3, name='c3')
        AdditionalCustomerData.objects.create(customer = self.c4, name='c4')
        AdditionalCustomerData.objects.create(customer = self.c5, name='c5')
        self.c1.friends.add(self.c2,self.c3,self.c4,self.c5)
        self.c2.friends.add(self.c3,self.c4,self.c5)
        self.c3.friends.add(self.c4,self.c5)
        self.c4.friends.add(self.c5)
        self.c5.friends.add(self.c1, self.c2)
        
        self.g1 = CustomerGroup.objects.create(name='Group1')
        self.g2 = CustomerGroup.objects.create(name='Group2')
        self.g3 = CustomerGroup.objects.create(name='Group3')
        
        self.g1.customers.add(self.c1)
        self.g1.customers.add(self.c2)
        self.g1.customers.add(self.c4)
        
        
        self.g2.customers.add(self.c2)
        self.g2.customers.add(self.c5)
        self.g3.customers.add(self.c3)

    def testBasic(self):
        
        # Simple constant value selects
        self.assertEquals(list(SELECT(3).query()), [3, ])
        self.assertEquals(list(SELECT(3/3).query()), [1, ])
        self.assertEquals(list(SELECT("String").query()), ["String", ])
        
        now = datetime.datetime.today().date()
        self.assertEquals(list(SELECT(now).query()), [now, ])
        
        #Select with fk
        acd = ModelSource(AdditionalCustomerData)
        customer = acd.customer
        check(self, 
              SELECT([customer, acd.name]),
              [(self.c1, 'c1'), (self.c2, 'c2'), (self.c3, 'c3'), (self.c4, 'c4'), (self.c5, 'c5')])
        
        #Select with related name
        customer = ModelSource(Customer)
        check(self,
              SELECT([customer, customer.additionalcustomerdata_set.name]),
              [(self.c1, 'c1'), (self.c2, 'c2'), (self.c3, 'c3'), (self.c4, 'c4'), (self.c5, 'c5')])
        
        #Selects
        check(self,
              SELECT([('pid', customer.id), ('ptype', customer.type)]),
              [(pl.id, pl.type) for pl in Customer.objects.all()])
        
        check(self,
              SELECT([('ptype', customer.type), ('pid', customer.id)]),
              [(pl.type, pl.id) for pl in Customer.objects.all()])
        
        check(self,
              SELECT([('ptype', customer.type), (customer.id)], limit = 2),
              [(pl.type, pl.id) for pl in Customer.objects.all()[:2]])
        
        check(self,
              SELECT([(customer.type), ('pid', customer.id)], limit = 3),
              [(pl.type, pl.id) for pl in Customer.objects.all()[:3]])
        
        check(self,
              SELECT(customer, limit = 2),
              Customer.objects.all()[:2])
        
        
        #Aggregations
        check(self,
              SELECT(COUNT(customer.id)),
              [Customer.objects.count()])
        
        
        #Selects with where
        check(self,
              SELECT([customer.id], where = (customer.id > 2) & (customer.id < 5), order_by=customer.id),
              [3, 4])
        check(self,
              SELECT(customer, where = (customer.id == 2)),
              [self.c2])
        
        check(self,
              SELECT(customer, where = (customer.additionalcustomerdata_set.name == 'c2')),
              [self.c2])

        
        #Set operations
        union = UNION(SELECT([("one",1), ("two", 2)]), SELECT([("three",3), ("four", 4)]))
        check(self,
              SELECT(union.one),
              [1,3])

        sel1 = SELECT([("id", customer.id)], where = (customer.id > 1))
        sel2 = SELECT([("id", customer.id)], where = (customer.id < 3))
        union = UNION_ALL(sel1, sel2)
        
        check(self,
              SELECT(COUNT(union.id)),
              [6])
        
        check(self,
              SELECT(SELECT(COUNT(sel1.id)) + SELECT(COUNT(sel2.id))),
              [6])

        #Joins
        sel1.JOIN(sel2, sel1.id == sel2.id)
        check(self,
              SELECT(COUNT(sel2.id)),
              [1])
        
        #many to many
        group = ModelSource(CustomerGroup)
        check(self,
              SELECT(group.customers, where = (group == self.g1), order_by = group.customers.id),
              [self.c1, self.c2, self.c4])
        
        check(self,
              SELECT(customer.groups, where = (customer == self.c2), order_by = customer.groups.id),
              [self.g1, self.g2])